﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel;

namespace Fotobanka
{
    /// <summary>
    /// Obsluzna trida pro praci s galeriemi
    /// </summary>
    public class GalleriesManager
    {
        SqlConnection connection;
        SqlCommand command;
        SqlDataReader reader;

        /// <summary>
        /// Metoda pro vyber prirazenych galerii uzivateli
        /// </summary>
        /// <param name="li">Object LoginInfo</param>
        /// <returns>seznam dostupnych galerii</returns>
        public List<GalleryObject> SelectAssignedGalleriesForUser(LoginInfo li)
        {
            List<GalleryObject> l = new List<GalleryObject>();
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                //prozatim se vybiraji pouze galerie,ktere maji skupiny a v nich je dany uzivatel
                connection.Open();
                command = new SqlCommand("SELECT Galleries.id, Galleries.name, Galleries.description, Galleries.lock_version, Galleries.create_on, Galleries.update_on, Galleries.hide FROM Galleries INNER JOIN Galerries_Groups ON Galleries.id = Galerries_Groups.gallery_id INNER JOIN Groups_Users ON Galerries_Groups.group_id = Groups_Users.group_id INNER JOIN User_Galleries ON User_Galleries.gallery_id = Galleries.id WHERE (Galleries.hide = 0) AND (Groups_Users.user_id = @id) OR (Galleries.hide = 0) AND (Galleries.user_id = @id) OR (Galleries.hide = 0) AND (User_Galleries.user_id = @id) GROUP BY Galleries.id, Galleries.name, Galleries.description, Galleries.lock_version, Galleries.create_on, Galleries.update_on, Galleries.hide ORDER BY Galleries.id", connection);
                command.Parameters.Add(new SqlParameter("@id", li.Id));

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    GalleryObject o = new GalleryObject();
                    o.Id = (int)reader["id"];
                    o.Name = reader["name"].ToString();
                    o.Description = reader["description"].ToString();
                    o.Lock = (byte)reader["lock_version"];
                    o.CreatedOn = (DateTime)reader["create_on"];
                    o.UpdatedOn = (DateTime)reader["update_on"];
                    o.Hidden = (byte)reader["hide"];
                    //o.GalleryAdmin = reader["email"].ToString();
                    l.Add(o);
                }
            }

            return l;
        }

        /// <summary>
        /// Vraci seznam ID galerii do kterych ma uzivatel pristup
        /// </summary>
        /// <param name="li">Object LoginInfo</param>
        /// <returns>seznam id dostupnych galerii</returns>
        public List<int> SelectAssignedGalleriesIdForUser(int id_user)
        {
            List<int> l = new List<int>();
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                //prozatim se vybiraji pouze galerie,ktere maji skupiny a v nich je dany uzivatel
                connection.Open();
                command = new SqlCommand("SELECT Galleries.id FROM Galleries INNER JOIN Galerries_Groups ON Galleries.id = Galerries_Groups.gallery_id INNER JOIN Groups_Users ON Galerries_Groups.group_id = Groups_Users.group_id INNER JOIN User_Galleries ON User_Galleries.gallery_id = Galleries.id WHERE (Galleries.hide = 0) AND (Groups_Users.user_id = @id) OR (Galleries.hide = 0) AND (Galleries.user_id = @id) OR (Galleries.hide = 0) AND (User_Galleries.user_id = @id) GROUP BY Galleries.id ORDER BY Galleries.id", connection);
                command.Parameters.Add(new SqlParameter("@id", id_user));

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    l.Add((int)reader["id"]);
                }
            }

            return l;
        }

        /// <summary>
        /// Pro Admina vyhleda podle emailu galerie uzivatele
        /// </summary>
        /// <param name="email">email uzivatele</param>
        /// <returns>seznam galerii vlastnenych uzivatelem 'email'</returns>
        public List<GalleryObject> SearchGalleriesViaEmailForAdmin(string email)
        {
            List<GalleryObject> l = new List<GalleryObject>();
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                //prozatim se vybiraji pouze galerie,ktere maji skupiny a v nich je dany uzivatel
                connection.Open();
                command = new SqlCommand("SELECT Galleries.id AS gid, Galleries.name, Galleries.description, Galleries.lock_version, Galleries.create_on, Galleries.update_on, Galleries.hide FROM Galleries, Users WHERE Users.email=@mail AND Users.id=Galleries.user_id", connection);
                command.Parameters.Add(new SqlParameter("@mail", email));

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    GalleryObject o = new GalleryObject();
                    o.Id = (int)reader["gid"];
                    o.Name = reader["name"].ToString();
                    o.Description = reader["description"].ToString();
                    o.Lock = (byte)reader["lock_version"];
                    o.CreatedOn = (DateTime)reader["create_on"];
                    o.UpdatedOn = (DateTime)reader["update_on"];
                    o.Hidden = (byte)reader["hide"];
                    //o.GalleryAdmin = reader["email"].ToString();
                    l.Add(o);
                }
            }

            return l;
        }

        /// <summary>
        /// Pro bezneho uzivatele vyhleda podle emailu galerie uzivatele
        /// </summary>
        /// <param name="email">email uzivatele</param>
        /// <returns>seznam galerii vlastnenych uzivatelem 'email'</returns>
        public List<GalleryObject> SearchGalleriesViaEmailForNoAdmin(string email)
        {
            List<GalleryObject> l = new List<GalleryObject>();
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                //prozatim se vybiraji pouze galerie,ktere maji skupiny a v nich je dany uzivatel
                connection.Open();
                command = new SqlCommand("SELECT Galleries.id AS gid, Galleries.name, Galleries.description, Galleries.lock_version, Galleries.create_on, Galleries.update_on, Galleries.hide FROM Galleries, Users WHERE Users.email=@mail AND Users.id=Galleries.user_id AND hide=0", connection);
                command.Parameters.Add(new SqlParameter("@mail", email));

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    GalleryObject o = new GalleryObject();
                    o.Id = (int)reader["gid"];
                    o.Name = reader["name"].ToString();
                    o.Description = reader["description"].ToString();
                    o.Lock = (byte)reader["lock_version"];
                    o.CreatedOn = (DateTime)reader["create_on"];
                    o.UpdatedOn = (DateTime)reader["update_on"];
                    o.Hidden = (byte)reader["hide"];
                    //o.GalleryAdmin = reader["email"].ToString();
                    l.Add(o);
                }
            }

            return l;
        }

        /// <summary>
        /// Pro admina vyhleda galerie podle popisu galerie
        /// </summary>
        /// <param name="desc">popis galerie</param>
        /// <returns>seznam galerii</returns>
        public List<GalleryObject> SearchGalleriesViaDescriptionForAdmin(string desc)
        {
            List<GalleryObject> l = new List<GalleryObject>();
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                //prozatim se vybiraji pouze galerie,ktere maji skupiny a v nich je dany uzivatel
                connection.Open();
                command = new SqlCommand("SELECT DISTINCT Galleries.id AS gid, Galleries.name, Galleries.description, Galleries.lock_version, Galleries.create_on, Galleries.update_on, Galleries.hide FROM Galleries , Photos WHERE Photos.gallery_id=Galleries.id AND Photos.title LIKE @desc UNION SELECT DISTINCT Galleries.id AS gid, Galleries.name, Galleries.description, Galleries.lock_version, Galleries.create_on, Galleries.update_on, Galleries.hide FROM Galleries WHERE description LIKE @desc", connection);
                command.Parameters.Add(new SqlParameter("@desc", "%" + desc + "%"));

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    GalleryObject o = new GalleryObject();
                    o.Id = (int)reader["gid"];
                    o.Name = reader["name"].ToString();
                    o.Description = reader["description"].ToString();
                    o.Lock = (byte)reader["lock_version"];
                    o.CreatedOn = (DateTime)reader["create_on"];
                    o.UpdatedOn = (DateTime)reader["update_on"];
                    o.Hidden = (byte)reader["hide"];
                    //o.GalleryAdmin = reader["email"].ToString();
                    l.Add(o);
                }
            }

            return l;
        }

        /// <summary>
        /// Pro bezneho uzivatele vyhleda galerie podle popisu
        /// </summary>
        /// <param name="desc">popis galerie</param>
        /// <returns>seznam nalezenych galerii</returns>
        public List<GalleryObject> SearchGalleriesViaDescriptionForNoAdmin(string desc)
        {
            List<GalleryObject> l = new List<GalleryObject>();
            using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
            {
                //prozatim se vybiraji pouze galerie,ktere maji skupiny a v nich je dany uzivatel
                connection.Open();
                command = new SqlCommand("SELECT DISTINCT Galleries.id AS gid, Galleries.name, Galleries.description, Galleries.lock_version, Galleries.create_on, Galleries.update_on, Galleries.hide FROM Galleries , Photos WHERE Photos.gallery_id=Galleries.id AND Photos.title LIKE @desc AND hide=0 UNION SELECT DISTINCT Galleries.id AS gid, Galleries.name, Galleries.description, Galleries.lock_version, Galleries.create_on, Galleries.update_on, Galleries.hide FROM Galleries WHERE description LIKE @desc AND hide=0", connection);
                command.Parameters.Add(new SqlParameter("@desc", "%" + desc + "%"));

                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    GalleryObject o = new GalleryObject();
                    o.Id = (int)reader["gid"];
                    o.Name = reader["name"].ToString();
                    o.Description = reader["description"].ToString();
                    o.Lock = (byte)reader["lock_version"];
                    o.CreatedOn = (DateTime)reader["create_on"];
                    o.UpdatedOn = (DateTime)reader["update_on"];
                    o.Hidden = (byte)reader["hide"];
                    //o.GalleryAdmin = reader["email"].ToString();
                    l.Add(o);
                }
            }

            return l;
        }

        /// <summary>
        /// Vyhleda uzivateli prirazene galerie podle jeho id
        /// </summary>
        /// <param name="user_id">ID uzivatele</param>
        /// <returns>seznam dostupnych galerii</returns>
        public List<GalleryObject> SelectAssignedGalleriesForUser(int user_id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    List<GalleryObject> l = new List<GalleryObject>();
                    //prozatim se vybiraji pouze galerie,ktere maji skupiny a v nich je dany uzivatel
                    connection.Open();
                    command = new SqlCommand("SELECT * FROM Galleries,Galerries_Groups,Groups_Users WHERE Galleries.id=Galerries_Groups.gallery_id AND Galerries_Groups.group_id=Groups_Users.group_id AND Groups_Users.user_id=@id AND Galleries.hide=0", connection);
                    command.Parameters.Add(new SqlParameter("@id", user_id));

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        GalleryObject o = new GalleryObject();
                        o.Id = (int)reader["id"];
                        o.Name = reader["name"].ToString();
                        o.Description = reader["description"].ToString();
                        o.Lock = (byte)reader["lock_version"];
                        o.CreatedOn = (DateTime)reader["create_on"];
                        o.UpdatedOn = (DateTime)reader["update_on"];
                        o.Hidden = (byte)reader["hide"];
                        //o.GalleryAdmin = reader["email"].ToString();
                        l.Add(o);
                    }

                    return l;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new List<GalleryObject>();
        }

        /// <summary>
        /// Vyhleda vsechny dostupne galerie
        /// </summary>
        /// <returns>seznam galerii</returns>
        public List<GalleryObject> SelectAllGalleries()
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    List<GalleryObject> l = new List<GalleryObject>();

                    connection.Open();
                    command = new SqlCommand("SELECT Galleries.id, Galleries.name, Galleries.description," +
                    " Galleries.user_id, Galleries.lock_version, Galleries.create_on, Galleries.update_on," +
                    " Galleries.hide, Users.id AS Expr1, Users.email FROM Galleries INNER JOIN Users ON" +
                    " Galleries.user_id = Users.id", connection);

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        GalleryObject o = new GalleryObject();
                        o.Id = (int)reader["id"];
                        o.Name = reader["name"].ToString();
                        o.Description = reader["description"].ToString();
                        o.Lock = (byte)reader["lock_version"];
                        o.CreatedOn = (DateTime)reader["create_on"];
                        o.UpdatedOn = (DateTime)reader["update_on"];
                        o.Hidden = (byte)reader["hide"];
                        o.GalleryAdmin = reader["email"].ToString();
                        l.Add(o);
                    }

                    return l;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new List<GalleryObject>();
        }

        /// <summary>
        /// Vyhleda skupiny ke galerii
        /// </summary>
        /// <param name="id">id galerie</param>
        /// <returns>seznam skupin</returns>
        public List<GroupObject> SelectGalleryGroups(int id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    List<GroupObject> l = new List<GroupObject>();

                    connection.Open();
                    command = new SqlCommand("SELECT * FROM Galerries_Groups,Groups WHERE gallery_id=@id AND group_id=Groups.id", connection);
                    command.Parameters.Add(new SqlParameter("@id", id));

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        GroupObject o = new GroupObject();
                        o.Id = (int)reader["id"];
                        o.Name = reader["name"].ToString();
                        o.Description = reader["description"].ToString();
                        o.Lock = (byte)reader["lock_version"];
                        o.CreatedOn = (DateTime)reader["create_on"];
                        o.UpdatedOn = (DateTime)reader["update_on"];
                        o.Deleteable = (bool)reader["deleteable"];
                        //o.GroupAdmin = reader["email"].ToString();
                        l.Add(o);
                    }

                    return l;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new List<GroupObject>();
        }

        /// <summary>
        /// Vyhleda seznam galerii vybraneho uzivatele
        /// </summary>
        /// <param name="user_id">id uzivatele</param>
        /// <returns>seznam galerii</returns>
        public List<GalleryObject> SelectUserGalleries(int user_id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    List<GalleryObject> l = new List<GalleryObject>();

                    connection.Open();
                    command = new SqlCommand("SELECT Galleries.id, Galleries.name, Galleries.description," +
                    " Galleries.user_id, Galleries.lock_version, Galleries.create_on, Galleries.update_on," +
                    " Galleries.hide, Users.id AS Expr1, Users.email FROM Galleries INNER JOIN Users ON" +
                    " Galleries.user_id = Users.id AND Galleries.user_id=@id", connection);
                    command.Parameters.Add(new SqlParameter("@id", user_id));

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        GalleryObject o = new GalleryObject();
                        o.Id = (int)reader["id"];
                        o.Name = reader["name"].ToString();
                        o.Description = reader["description"].ToString();
                        o.Lock = (byte)reader["lock_version"];
                        o.CreatedOn = (DateTime)reader["create_on"];
                        o.UpdatedOn = (DateTime)reader["update_on"];
                        o.Hidden = (byte)reader["hide"];
                        o.GalleryAdmin = reader["email"].ToString();
                        l.Add(o);
                    }

                    return l;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new List<GalleryObject>();
        }

        /// <summary>
        /// Vyhleda detail o galerii
        /// </summary>
        /// <param name="id">Id galerie</param>
        /// <returns>detail galerie</returns>
        public GalleryObject SelectGalleryDetail(int id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    GalleryObject o = new GalleryObject();

                    connection.Open();
                    command = new SqlCommand("SELECT * FROM Galleries WHERE id=@id", connection);
                    command.Parameters.Add(new SqlParameter("@id", id));

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {

                        o.Id = (int)reader["id"];
                        o.Name = reader["name"].ToString();
                        o.Description = reader["description"].ToString();
                        o.Lock = (byte)reader["lock_version"];
                        o.CreatedOn = (DateTime)reader["create_on"];
                        o.UpdatedOn = (DateTime)reader["update_on"];
                        o.Hidden = (byte)reader["hide"];
                        //o.GalleryAdmin = reader["email"].ToString();
                        o.GalleryAdminId = Convert.ToInt32(reader["user_id"].ToString());
                    }

                    return o;

                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return new GalleryObject();
        }

        /// <summary>
        /// Provede update udaju galerie
        /// </summary>
        /// <param name="o">galerie</param>
        public void UpdateGalleryDetail(GalleryObject o)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("UPDATE Galleries SET name=@jmeno, description=@popis, hide=@hide, update_on=@updated WHERE id=@id;", connection);
                    command.Parameters.Add(new SqlParameter("@jmeno", o.Name));
                    command.Parameters.Add(new SqlParameter("@popis", o.Description));
                    //command.Parameters.Add(new SqlParameter("@userid", id));
                    command.Parameters.Add(new SqlParameter("@updated", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("@hide", o.Hidden));
                    command.Parameters.Add(new SqlParameter("@id", o.Id));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        /// <summary>
        /// Vlozi galerii do databaze
        /// </summary>
        /// <param name="name">nazev galerie</param>
        /// <param name="popis">popis galerie</param>
        /// <param name="hide">skryta / viditelna</param>
        /// <param name="id">id uzivatele</param>
        public void InsertToDb(string name, string popis, bool hide, int id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("INSERT INTO Galleries  (name, description, user_id, create_on, update_on, hide) VALUES (@jmeno, @popis, @userid, @created, @updated, @hide);", connection);
                    command.Parameters.Add(new SqlParameter("@jmeno", name));
                    command.Parameters.Add(new SqlParameter("@popis", popis));
                    command.Parameters.Add(new SqlParameter("@userid", id));
                    command.Parameters.Add(new SqlParameter("@created", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("@updated", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("@hide", hide));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        /// <summary>
        /// Vlozi galerii do skupiny
        /// </summary>
        /// <param name="gallery_id">id galerie</param>
        /// <param name="group_id">id skupiny</param>
        public void InsertGroupToGallery(int gallery_id, int group_id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("INSERT INTO Galerries_Groups  (gallery_id, group_id) VALUES (@gallery_id, @group_id);", connection);
                    command.Parameters.Add(new SqlParameter("@gallery_id", gallery_id));
                    command.Parameters.Add(new SqlParameter("@group_id", group_id));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        /// <summary>
        /// Kontrola jestli galerie uz existuje
        /// </summary>
        /// <param name="jmeno">nazev galerie</param>
        /// <param name="id">id uzivatele</param>
        /// <returns>true-existuje, false-neexistuje</returns>
        public bool GalleryAlreadyExist(string jmeno, int id)
        {
            bool exist = false;
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("SELECT id FROM Galleries WHERE name=@value AND user_id=@id;", connection);
                    command.Parameters.Add(new SqlParameter("@value", jmeno));
                    command.Parameters.Add(new SqlParameter("@id", id));

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        exist = true;
                    else
                        exist = false;
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (reader != null && reader.IsClosed == false)
                    reader.Close();
            }

            return exist;
        }

        /// <summary>
        /// Kontrola jestli galerie uz existuje
        /// </summary>
        /// <param name="jmeno">nazev galerie</param>
        /// <param name="id">id uzivatele</param>
        /// <param name="gallery_id">id galerie</param>
        /// <returns>true-existuje, false-neexistuje</returns>
        public bool GalleryAlreadyExist(string jmeno, int id, int gallery_id)
        {
            bool exist = false;
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("SELECT id FROM Galleries WHERE name=@value AND user_id=@id AND id<>@gid", connection);
                    command.Parameters.Add(new SqlParameter("@value", jmeno));
                    command.Parameters.Add(new SqlParameter("@id", id));
                    command.Parameters.Add(new SqlParameter("@gid", gallery_id));

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        exist = true;
                    else
                        exist = false;
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (reader != null && reader.IsClosed == false)
                    reader.Close();
            }

            return exist;
        }

        /// <summary>
        /// Kontrola jestli uz je galerie ve skupine
        /// </summary>
        /// <param name="gallery_id">id galerie</param>
        /// <param name="group_id">id skupiny</param>
        /// <returns>true-existuje, false-neexistuje</returns>
        public bool GroupExistInGallery(int gallery_id, int group_id)
        {
            bool exist = false;
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("SELECT id FROM Galerries_Groups WHERE group_id=@group_id AND gallery_id=@id;", connection);
                    command.Parameters.Add(new SqlParameter("@group_id", group_id));
                    command.Parameters.Add(new SqlParameter("@id", gallery_id));

                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                        exist = true;
                    else
                        exist = false;
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
                if (reader != null && reader.IsClosed == false)
                    reader.Close();
            }

            return exist;
        }

        /// <summary>
        /// Smaze galerii z databaze
        /// </summary>
        /// <param name="Id">id galerie</param>
        public void DeleteFromDB(int Id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("DELETE FROM Galleries WHERE (id = @id);", connection);
                    command.Parameters.Add(new SqlParameter("@id", Id));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        /// <summary>
        /// Smaze skupinu galerie
        /// </summary>
        /// <param name="Id">id skupiny</param>
        public void DeleteGroupFromGallery(int Id)
        {
            try
            {
                using (connection = new SqlConnection(ConfigurationManager.ConnectionStrings["amalka"].ToString()))
                {
                    connection.Open();

                    command = new SqlCommand("DELETE FROM Galerries_Groups WHERE (id = @id);", connection);
                    command.Parameters.Add(new SqlParameter("@id", Id));

                    command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlexc)
            {
                Console.WriteLine(sqlexc.Message);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }
    }
}
